package com.leiou.stu_list;

import com.leiou.pojo.Student;
import com.leiou.utils.ConnectionDatabaseForSQLSeverUseJdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/*
 *蒋碧鹏
 *
 */
public class StulistDaoImpl implements StulistDao {
    Connection connection = null;

    PreparedStatement pstmt = null;
    ResultSet rset = null;

    public StulistDaoImpl() {
        connection = ConnectionDatabaseForSQLSeverUseJdbc.getConnection();
    }

    @Override
    public List<Student> findAllstus(int page, int size) {
        List<Student> list = new ArrayList<>();
        try {
            // 验证数据库连接状态
            if (connection == null || connection.isClosed()) {
                connection = ConnectionDatabaseForSQLSeverUseJdbc.getConnection();
            }

            // 使用SQL Server 2008兼容的分页查询语法
            pstmt = connection.prepareStatement(
                    "WITH OrderedStudents AS (SELECT *, ROW_NUMBER() OVER (ORDER BY studentId) AS RowNum FROM Student) "
                            +
                            "SELECT studentId, name, gender, birthDate, idCard, college, major, className, status, dormId, bedNumber, phone, email, emergencyContact, username, password FROM OrderedStudents WHERE RowNum BETWEEN ? AND ? ORDER BY RowNum");
            int start = (page - 1) * size + 1;
            int end = page * size;
            pstmt.setInt(1, start);
            pstmt.setInt(2, end);
            rset = pstmt.executeQuery();

            Student student = null;
            while (rset.next()) {
                student = new Student(
                        rset.getString("password"),
                        rset.getString("username"),
                        rset.getString("emergencyContact"),
                        rset.getString("email"),
                        rset.getString("phone"),
                        rset.getString("bedNumber"),
                        rset.getInt("dormId"),
                        rset.getInt("status"),
                        rset.getString("className"),
                        rset.getString("major"),
                        rset.getString("college"),
                        rset.getString("idCard"),
                        rset.getDate("birthDate") != null ? rset.getDate("birthDate").toLocalDate() : null,
                        rset.getString("gender"),
                        rset.getString("name"),
                        rset.getString("studentId"));
                list.add(student);

            }
            return list;

        } catch (SQLException ex) {
            System.err.println("分页查询学生失败 - 页码: " + page + ", 每页大小: " + size + ", 错误: " + ex.getMessage());
            ex.printStackTrace();
            throw new RuntimeException("数据库查询失败", ex);
        } finally {
            try {
                if (rset != null)
                    rset.close();
                if (pstmt != null)
                    pstmt.close();
            } catch (Exception e) {
                System.err.println("资源关闭错误: " + e.getMessage());
            }
        }

    }

    // 实现模糊查询
    @Override
    public List<Student> findStudentByKeyword(String keyword) {
        List<Student> list = new ArrayList<>();
        try {

            pstmt = connection.prepareStatement(
                    "select  studentId, name,gender,birthDate,idCard,college,major,className,status,dormId,bedNumber,phone,email,emergencyContact,username,password from Student where studentId like ? or name like ? or gender like ? or idCard like ? or college like ? or major like ? or className like ?");
            pstmt.setString(1, "%" + keyword + "%");
            pstmt.setString(2, "%" + keyword + "%");
            pstmt.setString(3, "%" + keyword + "%");
            pstmt.setString(4, "%" + keyword + "%");
            pstmt.setString(5, "%" + keyword + "%");
            pstmt.setString(6, "%" + keyword + "%");
            pstmt.setString(7, "%" + keyword + "%");
            rset = pstmt.executeQuery();
            Student student = null;
            while (rset.next()) {
                student = new Student(
                        rset.getString("password"),
                        rset.getString("username"),
                        rset.getString("emergencyContact"),
                        rset.getString("email"),
                        rset.getString("phone"),
                        rset.getString("bedNumber"),
                        rset.getInt("dormId"),
                        rset.getInt("status"),
                        rset.getString("className"),
                        rset.getString("major"),
                        rset.getString("college"),
                        rset.getString("idCard"),
                        rset.getDate("birthDate") != null ? rset.getDate("birthDate").toLocalDate() : null,
                        rset.getString("gender"),
                        rset.getString("name"),
                        rset.getString("studentId"));
                list.add(student);
            }
            return list;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    // 毕业按钮
    @Override
    public int deleteStudent(String id) {
        // Integer studentId = id != null ? Integer.parseInt(String.valueOf(id)) : null;
        try {
            // 1为在读0为毕业
            pstmt = connection.prepareStatement("update  Student set status=0 where studentId =   ?");
            pstmt.setString(1, id);
            int i = pstmt.executeUpdate();
            return i == 1 ? 1 : 0;

        } catch (Exception e) {
            e.printStackTrace();
            return 0;
        }

    }

    // 批量毕业按钮
    @Override
    public int deleteStudents(List<String> ids) {
        int count = 0;
        try {
            StringBuilder sql = new StringBuilder("update  Student set status=0 where studentId IN (");
            for (int i = 0; i < ids.size(); i++) {
                if (i > 0) {
                    sql.append(",");
                }
                sql.append("?");
            }
            sql.append(")");
            pstmt = connection.prepareStatement(sql.toString());
            for (int i = 0; i < ids.size(); i++) {
                pstmt.setString(i + 1, ids.get(i));
            }
            count = pstmt.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return count;
    }

}
